from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import pandas as pd

def process_merged_cells(ws):
    """处理合并单元格：将合并区域的值填充到每个单元格"""
    if not ws.merged_cells.ranges:
        return
        
    merged_ranges = list(ws.merged_cells.ranges)
    
    for merged_range in merged_ranges:
        min_row, min_col, max_row, max_col = merged_range.min_row, merged_range.min_col, merged_range.max_row, merged_range.max_col
        top_left_value = ws.cell(row=min_row, column=min_col).value
        
        # 解除合并
        ws.unmerge_cells(str(merged_range))
        
        # 填充值到所有单元格
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                if row == min_row and col == min_col:
                    continue  # 保留左上角原始值
                ws.cell(row=row, column=col, value=top_left_value)

def remove_trailing_empty(ws):
    """快速移除工作表末尾的空行和空列"""
    print(f"开始快速清理工作表: {ws.title}")
    
    # 找到实际数据的边界
    last_row_with_data = 0
    last_col_with_data = 0
    
    print("扫描实际数据边界...")
    for row in ws.iter_rows():
        for col_idx, cell in enumerate(row, 1):
            if cell.value is not None and cell.value != "":
                last_row_with_data = max(last_row_with_data, cell.row)
                last_col_with_data = max(last_col_with_data, col_idx)
    
    print(f"实际数据边界: {last_row_with_data} 行 x {last_col_with_data} 列")
    
    # 删除超出边界的行和列
    if last_row_with_data < ws.max_row:
        rows_to_delete = ws.max_row - last_row_with_data
        print(f"删除 {rows_to_delete} 个空行")
        ws.delete_rows(last_row_with_data + 1, rows_to_delete)
    
    if last_col_with_data < ws.max_column:
        cols_to_delete = ws.max_column - last_col_with_data
        print(f"删除 {cols_to_delete} 个空列")
        ws.delete_cols(last_col_with_data + 1, cols_to_delete)
    
    print(f"快速清理完成! 最终尺寸: {ws.max_row} 行 x {ws.max_column} 列")

def sheet_to_dataframe(ws):
    """将工作表转换为DataFrame"""
    # 1. 处理合并单元格
    process_merged_cells(ws)
    
    # 2. 移除末尾空行/空列
    remove_trailing_empty(ws)
    
    # 3. 转换为DataFrame
    data = ws.values
    cols = next(data)
    df = pd.DataFrame(data, columns=cols)

    # 4. 使用pandas清理空行空列
    df = df.dropna(how='all')  # 删除全空行
    
    # 5. 清理空列和无效列名
    # 先删除列名为None或空的列
    df = df.loc[:, df.columns.notna()]  # 删除列名为None的列
    df = df.loc[:, df.columns != ""]    # 删除列名为空字符串的列
    
    # 再删除值全为空的列
    df = df.dropna(axis=1, how='all') 

    return df